# %%HTML

Data Analysis Report

Prepared By: Zahiruddin Zahidanishah

from IPython.display import display_html
def display_side_by_side(*args):
    html_str=''
    for df in args:
        html_str+=df.to_html()
    display_html(html_str.replace('table','table style="display:inline"'),raw=True)

from IPython.display import HTML

HTML('''<script>
code_show=true; 
function code_toggle() {
 if (code_show){
 $('div.input').hide();
 } else {
 $('div.input').show();
 }
 code_show = !code_show
} 
$( document ).ready(code_toggle);
</script>
<form action="javascript:code_toggle()"><input type="submit" value="Click here to toggle on/off the raw code."></form>''')

Electricity Consumption Report

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import plotly.graph_objects as go
import plotly.express as px
from plotly.subplots import make_subplots

#data = {'Month':['Jan', 'Feb'], 'Usage':[355,314], 'Amount':[107.05,85.55]}
#df = pd.DataFrame(data, columns=['Month', 'Usage', 'Amount'])
#df.to_excel('utilities.xlsx', sheet_name='Sheet1')
df1 = pd.read_excel('elec_utils.xlsx', sheet_name='2014')
df2 = pd.read_excel('elec_utils.xlsx', sheet_name='2015')
df3 = pd.read_excel('elec_utils.xlsx', sheet_name='2016')
df4 = pd.read_excel('elec_utils.xlsx', sheet_name='2017')
df5 = pd.read_excel('elec_utils.xlsx', sheet_name='2018')
df6 = pd.read_excel('elec_utils.xlsx', sheet_name='2019')
df7 = pd.read_excel('elec_utils.xlsx', sheet_name='2020')
df8 = pd.read_excel('elec_utils.xlsx', sheet_name='2021')
df9 = pd.read_excel('elec_utils.xlsx', sheet_name='2022') 

Introduction

This report reports shows the electricity consumption and amount payable for a typical double storey house in urban city located in Selangor, Malaysia. This report consists of data from year 2014 to date. This report will shows the relation between the consumption and payment for the electricity usage every month and every year.

The details of the house inventory and areas are as describe belows.

data = {'Items':['Type','Land Area (sq.m)','Build Up Area (sq.m)','Bedrooms','Bathrooms',
                 'Lighting Pts.','Power Pts.','Fan Pts.','AC Pts.'],
        'Description':['Double Storey Terrace House',121,180,4,3,22,14,4,3]}
df_house = pd.DataFrame(data,columns=['Items','Description'])
df_house.style.set_caption("Assets Details")                   

Assets Details

	Items	Description
0	Type	Double Storey Terrace House
1	Land Area (sq.m)	121
2	Build Up Area (sq.m)	180
3	Bedrooms	4
4	Bathrooms	3
5	Lighting Pts.	22
6	Power Pts.	14
7	Fan Pts.	4
8	AC Pts.	3

data = {'Year':[2014,2015,2016,2017,2018,2019,2020,2021,2022],
       'Total Usage (kWh)':[df1['Usage (kWh)'].sum(),df2['Usage (kWh)'].sum(),df3['Usage (kWh)'].sum(),
                            df4['Usage (kWh)'].sum(),df5['Usage (kWh)'].sum(),df6['Usage (kWh)'].sum(),
                            df7['Usage (kWh)'].sum(),df8['Usage (kWh)'].sum(),df9['Usage (kWh)'].sum()],
        'Ave. Usage/P (kWh/p)':[df1['Usage (kWh)'].sum()/5/12,df2['Usage (kWh)'].sum()/5/12,
                                        df3['Usage (kWh)'].sum()/5/12,df4['Usage (kWh)'].sum()/5/12,
                                        df5['Usage (kWh)'].sum()/5/12,df6['Usage (kWh)'].sum()/5/12,
                                        df7['Usage (kWh)'].sum()/5/12,df8['Usage (kWh)'].sum()/5/12,
                                        df9['Usage (kWh)'].sum()/5/12],
        'Ave. Usage/A (kWh/m2)':[df1['Usage (kWh)'].sum()/180/12,df2['Usage (kWh)'].sum()/180/12,
                                       df3['Usage (kWh)'].sum()/180/12,df4['Usage (kWh)'].sum()/180/12,
                                       df5['Usage (kWh)'].sum()/180/12,df6['Usage (kWh)'].sum()/180/12,
                                       df7['Usage (kWh)'].sum()/180/12,df8['Usage (kWh)'].sum()/180/12,
                                       df9['Usage (kWh)'].sum()/180/12],
       'Total Amount (RM)':[df1['Amount (RM)'].sum(),df2['Amount (RM)'].sum(),
                            df3['Amount (RM)'].sum(),df4['Amount (RM)'].sum(),
                            df5['Amount (RM)'].sum(),df6['Amount (RM)'].sum(),
                            df7['Amount (RM)'].sum(),df8['Amount (RM)'].sum(),
                            df9['Amount (RM)'].sum()],
       'Ave. Usage (kWh)':[df1['Usage (kWh)'].mean(),df2['Usage (kWh)'].mean(),
                           df3['Usage (kWh)'].mean(),df4['Usage (kWh)'].mean(),
                           df5['Usage (kWh)'].mean(),df6['Usage (kWh)'].mean(),
                           df7['Usage (kWh)'].mean(),df8['Usage (kWh)'].mean(),
                           df9['Usage (kWh)'].mean()],
       'Ave. Amount (RM)':[df1['Amount (RM)'].mean(),df2['Amount (RM)'].mean(),
                           df3['Amount (RM)'].mean(),df4['Amount (RM)'].mean(),
                           df5['Amount (RM)'].mean(),df6['Amount (RM)'].mean(),
                           df7['Amount (RM)'].mean(),df8['Amount (RM)'].mean(),
                           df9['Amount (RM)'].mean()],
       'Max. Usage (kWh)':[df1['Usage (kWh)'].max(),df2['Usage (kWh)'].max(),df3['Usage (kWh)'].max(),
                           df4['Usage (kWh)'].max(),df5['Usage (kWh)'].max(),df6['Usage (kWh)'].max(),
                           df7['Usage (kWh)'].max(),df8['Usage (kWh)'].max(),df9['Usage (kWh)'].max()],
       'Max. Amount (RM)':[df1['Amount (RM)'].max(),df2['Amount (RM)'].max(),
                           df3['Amount (RM)'].max(),df4['Amount (RM)'].max(),
                           df5['Amount (RM)'].max(),df6['Amount (RM)'].max(),
                           df7['Amount (RM)'].max(),df8['Amount (RM)'].max(),
                           df9['Amount (RM)'].max()]}
df = pd.DataFrame(data,columns=['Year','Total Usage (kWh)','Ave. Usage/P (kWh/p)','Ave. Usage/A (kWh/m2)',
                                'Ave. Usage (kWh)','Total Amount (RM)','Ave. Amount (RM)',
                                'Max. Usage (kWh)','Max. Amount (RM)']).astype(int)

df['Electricity Rate (RM/kWh)'] = df['Total Amount (RM)']/df['Total Usage (kWh)']
df = df.reindex(['Year',
                 'Total Usage (kWh)',
                 'Total Amount (RM)',
                 'Electricity Rate (RM/kWh)',
                 'Ave. Usage (kWh)',
                 'Ave. Amount (RM)',
                 'Ave. Usage/P (kWh/p)',
                 'Ave. Usage/A (kWh/m2)',
                 'Max. Usage (kWh)',
                 'Max. Amount (RM)'], axis=1)

df.style.set_caption("Summary of Annual Electriciy Consumption").set_table_styles([{
    'selector': 'caption',
    'props': [
        ('color', 'black'),
        ('font-size', '26px'),
        ("text-align", "center"),
        ('text-decoration', 'underline'),
        ('font-family','Arial'),
        ('text-shadow', '2px 2px 5px grey')
    ]},(dict
        (selector='th',props=[('text-align',
                               'left')]))]).format(
    {'Electricity Rate (RM/kWh)':'{:,.2f}'}
).set_properties(subset=['Year'],**{'text-align': 'left'}).hide_index()

Summary of Annual Electriciy Consumption

Year	Total Usage (kWh)	Total Amount (RM)	Electricity Rate (RM/kWh)	Ave. Usage (kWh)	Ave. Amount (RM)	Ave. Usage/P (kWh/p)	Ave. Usage/A (kWh/m2)	Max. Usage (kWh)	Max. Amount (RM)
2014	3645	964	0.26	303	80	60	1	350	102
2015	3569	899	0.25	297	74	59	1	405	103
2016	4498	1365	0.30	374	113	74	2	530	188
2017	4719	1491	0.32	393	124	78	2	448	153
2018	4640	1439	0.31	386	119	77	2	490	175
2019	4249	1278	0.30	354	106	70	1	444	151
2020	5080	1240	0.24	423	103	84	2	1221	164
2021	4596	1326	0.29	383	110	76	2	444	136
2022	1526	453	0.30	381	113	25	0	460	152

fig = make_subplots(
    rows=1, cols=2,
    specs=[[{"type": "xy"},{"type": "domain"}]],
    subplot_titles=('<b>Total Usage (kWh)</b>','<b>Total Amount (RM)</b>')
)

fig.add_trace(go.Bar(x = df['Year'], y = df['Total Usage (kWh)'],
                     name='Total Usage (kWh)'),row=1, col=1)

fig.add_trace(go.Pie(values=df['Total Amount (RM)'],
                     labels=df['Year'],
                     textinfo='label+percent'),row=1, col=2)

fig.update_layout(height=500, showlegend=False,
                 title_text='Electricity Usage & Consumption', title_x=0.5)
fig.update_annotations(font=dict(family="Helvetica", size=12))
fig.update_layout(font=dict(family="Helvetica", size=12))

fig.show()

bar_plots = [
    go.Bar(x = df['Year'], y = df['Ave. Usage/P (kWh/p)'],name='Total Consumption (kWh)')
           ]
layout = go.Layout(
title=go.layout.Title(text='Monthly Average Person Electricity Consumption In Respective Year',x=0.5),
yaxis_title='Usage (kWh/person)',xaxis_tickmode='array')
fig = go.Figure(data=bar_plots, layout=layout)
#fig.show()

bar_plots = [
    go.Bar(x = df['Year'], y = df['Ave. Usage/A (kWh/m2)'],name='Total Consumption (kWh)')
           ]
layout = go.Layout(
title=go.layout.Title(text='Monthly Average Electricity Consumption/Area In Respective Year',x=0.5),
yaxis_title='Usage (kWh/m2)',xaxis_tickmode='array')
fig = go.Figure(data=bar_plots, layout=layout)
fig.show()

bar_plots = [
    go.Bar(x = df['Year'], y = df['Total Usage (kWh)'],name='Total Consumption (kWh)'),
    go.Bar(x = df['Year'], y = df['Total Amount (RM)'],name='Total Amount (RM)')
           ]
layout = go.Layout(
title=go.layout.Title(text='Annual Electricity Consumption (kWh) & Amount (RM)',x=0.5),
yaxis_title='Usage (kWh)',xaxis_tickmode='array')
fig = go.Figure(data=bar_plots, layout=layout)
fig.show()

Detail of Electriciy Consumption and Amount for Respective Year

bar_plots = [
    go.Bar(x = df1[2014], y = df1['Usage (kWh)'],name=2014),
    go.Bar(x = df2[2015], y = df2['Usage (kWh)'],name=2015),
    go.Bar(x = df3[2016], y = df3['Usage (kWh)'],name=2016),
    go.Bar(x = df4[2017], y = df4['Usage (kWh)'],name=2017),
    go.Bar(x = df5[2018], y = df5['Usage (kWh)'],name=2018),
    go.Bar(x = df6[2019], y = df6['Usage (kWh)'],name=2019),
    go.Bar(x = df7[2020], y = df7['Usage (kWh)'],name=2020),
    go.Bar(x = df8[2021], y = df8['Usage (kWh)'],name=2021),
    go.Bar(x = df9[2022], y = df9['Usage (kWh)'],name=2022)
           ]
layout = go.Layout(
title=go.layout.Title(text='Monthly Electricity Consumption (kWh)',x=0.5),
yaxis_title='Usage (kWh)',xaxis_tickmode='array')
fig = go.Figure(data=bar_plots, layout=layout)
fig.show()

bar_plots = [
    go.Bar(x = df1[2014], y = df1['Amount (RM)'],name=2014),
    go.Bar(x = df2[2015], y = df2['Amount (RM)'],name=2015),
    go.Bar(x = df3[2016], y = df3['Amount (RM)'],name=2016),
    go.Bar(x = df4[2017], y = df4['Amount (RM)'],name=2017),
    go.Bar(x = df5[2018], y = df5['Amount (RM)'],name=2018),
    go.Bar(x = df6[2019], y = df6['Amount (RM)'],name=2019),
    go.Bar(x = df7[2020], y = df7['Amount (RM)'],name=2020),
    go.Bar(x = df8[2021], y = df8['Amount (RM)'],name=2021),
    go.Bar(x = df9[2022], y = df9['Amount (RM)'],name=2022)
           ]
layout = go.Layout(
title=go.layout.Title(text='Monthly Electricity Amount (RM)',x=0.5),
yaxis_title='Amount (RM)',xaxis_tickmode='array')
fig = go.Figure(data=bar_plots, layout=layout)
fig.show()

Detail of Consumption (kWh) and Amount (RM) for Every Years From 2014 to 2020

df1['kWh/person'] = df1['Usage (kWh)']/5
df2['kWh/person'] = df2['Usage (kWh)']/5
df3['kWh/person'] = df3['Usage (kWh)']/5
df4['kWh/person'] = df4['Usage (kWh)']/5
df5['kWh/person'] = df5['Usage (kWh)']/5
df6['kWh/person'] = df6['Usage (kWh)']/5
df7['kWh/person'] = df7['Usage (kWh)']/5
df8['kWh/person'] = df8['Usage (kWh)']/5
df9['kWh/person'] = df9['Usage (kWh)']/5
display_side_by_side(df1, df2, df3)

	2014	Usage (kWh)	Amount (RM)	kWh/person
0	Jan	261	63.98	52.2
1	Feb	288	70.56	57.6
2	Mac	348	101.77	69.6
3	Apr	263	64.64	52.6
4	May	350	102.80	70.0
5	Jun	324	89.38	64.8
6	Jul	346	100.74	69.2
7	Aug	268	66.31	53.6
8	Sep	292	74.33	58.4
9	Oct	267	65.98	53.4
10	Nov	301	77.52	60.2
11	Dec	337	86.34	67.4

	2015	Usage (kWh)	Amount (RM)	kWh/person
0	Jan	254	61.64	50.8
1	Feb	347	86.90	69.4
2	Mac	276	68.98	55.2
3	Apr	309	81.64	61.8
4	May	301	77.52	60.2
5	Jun	273	67.98	54.6
6	Jul	405	103.84	81.0
7	Aug	238	56.29	47.6
8	Sep	295	75.33	59.0
9	Oct	266	65.64	53.2
10	Nov	321	81.69	64.2
11	Dec	284	71.66	56.8

	2016	Usage (kWh)	Amount (RM)	kWh/person
0	Jan	246	58.96	49.2
1	Feb	399	120.01	79.8
2	Mac	530	188.20	106.0
3	Apr	465	162.14	93.0
4	May	494	177.10	98.8
5	Jun	342	98.67	68.4
6	Jul	305	79.58	61.0
7	Aug	409	125.46	81.8
8	Sep	336	87.80	67.2
9	Oct	378	117.25	75.6
10	Nov	286	72.32	57.2
11	Dec	308	78.16	61.6

display_side_by_side(df4, df5, df6)

	2017	Usage (kWh)	Amount (RM)	kWh/person
0	Jan	427	142.53	85.4
1	Feb	390	123.44	78.0
2	Mac	448	153.37	89.6
3	Apr	441	149.76	88.2
4	May	361	108.48	72.2
5	Jun	393	124.99	78.6
6	Jul	339	91.97	67.8
7	Aug	383	119.83	76.6
8	Sep	399	122.93	79.8
9	Oct	406	131.70	81.2
10	Nov	405	131.18	81.0
11	Dec	327	90.93	65.4

	2018	Usage (kWh)	Amount (RM)	kWh/person
0	Jan	342	98.67	68.4
1	Feb	455	149.20	91.0
2	Mac	426	142.02	85.2
3	Apr	419	138.40	83.8
4	May	490	175.04	98.0
5	Jun	315	78.88	63.0
6	Jul	357	106.41	71.4
7	Aug	412	134.79	82.4
8	Sep	389	115.14	77.8
9	Oct	358	106.93	71.6
10	Nov	368	112.09	73.6
11	Dec	309	81.64	61.8

	2019	Usage (kWh)	Amount (RM)	kWh/person
0	Jan	370	113.12	74.0
1	Feb	444	151.30	88.8
2	Mac	399	130.15	79.8
3	Apr	373	116.50	74.6
4	May	413	137.45	82.6
5	Jun	285	72.00	57.0
6	Jul	366	112.85	73.2
7	Aug	280	70.30	56.0
8	Sep	348	103.40	69.6
9	Oct	336	97.10	67.2
10	Nov	323	90.30	64.6
11	Dec	312	84.50	62.4

display_side_by_side(df7, df8, df9)

	2020	Usage (kWh)	Amount (RM)	kWh/person
0	Jan	355	107.05	71.0
1	Feb	314	85.55	62.8
2	Mac	350	104.45	70.0
3	Apr	350	96.35	70.0
4	May	350	88.80	70.0
5	Jun	1221	164.40	244.2
6	Jul	368	112.10	73.6
7	Aug	344	99.70	68.8
8	Sep	360	107.96	72.0
9	Oct	336	82.55	67.2
10	Nov	391	107.05	78.2
11	Dec	341	84.80	68.2

	2021	Usage (kWh)	Amount (RM)	kWh/person
0	Jan	322	79.55	64.4
1	Feb	378	111.55	75.6
2	Mac	378	111.55	75.6
3	Apr	361	103.00	72.2
4	May	357	100.95	71.4
5	Jun	393	119.15	78.6
6	Jul	361	103.00	72.2
7	Aug	436	122.28	87.2
8	Sep	396	108.60	79.2
9	Oct	444	136.60	88.8
10	Nov	404	124.65	80.8
11	Dec	366	105.50	73.2

	2022	Usage (kWh)	Amount (RM)	kWh/person
0	Jan	359.0	102.00	71.8
1	Feb	323.0	83.85	64.6
2	Mac	384.0	114.60	76.8
3	Apr	460.0	152.91	92.0
4	May	NaN	NaN	NaN
5	Jun	NaN	NaN	NaN
6	Jul	NaN	NaN	NaN
7	Aug	NaN	NaN	NaN
8	Sep	NaN	NaN	NaN
9	Oct	NaN	NaN	NaN
10	Nov	NaN	NaN	NaN
11	Dec	NaN	NaN	NaN

bar_plots = [
    go.Bar(x = df1[2014], y = df1['kWh/person'],name=2014),
    go.Bar(x = df2[2015], y = df2['kWh/person'],name=2015),
    go.Bar(x = df3[2016], y = df3['kWh/person'],name=2016),
    go.Bar(x = df4[2017], y = df4['kWh/person'],name=2017),
    go.Bar(x = df5[2018], y = df5['kWh/person'],name=2018),
    go.Bar(x = df6[2019], y = df6['kWh/person'],name=2019),
    go.Bar(x = df7[2020], y = df7['kWh/person'],name=2020),
    go.Bar(x = df8[2021], y = df8['kWh/person'],name=2021),
    go.Bar(x = df9[2022], y = df9['kWh/person'],name=2022)
           ]
layout = go.Layout(
title=go.layout.Title(text='Monthly kwh/person Usage',x=0.5),
yaxis_title='kWh/person',xaxis_tickmode='array')
fig = go.Figure(data=bar_plots, layout=layout)
fig.show()

Details of Monthly Electricity Usage

px.bar(df1, x = 2014, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2014, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df2, x = 2015, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2015, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df3, x = 2016, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2016, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df4, x = 2017, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2017, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df5, x = 2018, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2018, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df6, x = 2019, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2019, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df7, x = 2020, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2020, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df8, x = 2021, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2021, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df9, x = 2022, y = 'Usage (kWh)', title = 'Electricity Usage Annually', color=2022, color_continuous_scale=px.colors.sequential.Viridis)

Details of Monthly Electricity Cost

px.bar(df1, x = 2014, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2014, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df2, x = 2015, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2015, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df3, x = 2016, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2016, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df4, x = 2017, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2017, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df5, x = 2018, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2018, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df6, x = 2019, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2019, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df7, x = 2020, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2020, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df8, x = 2021, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2021, color_continuous_scale=px.colors.sequential.Viridis)

px.bar(df9, x = 2022, y = 'Amount (RM)', title = 'Electricity Payment Annually', color=2022, color_continuous_scale=px.colors.sequential.Viridis)

Note: This report is prepared by Zahiruddin Zahidanishah. This report is only for educational purposed and shall not be used for any commercial purposed.